﻿/*
 * Refferent
 * 
 * TCP/IP Socket 
 * 1.  http://tech.pro/tutorial/704/csharp-tutorial-simple-threaded-tcp-server
 * 2.  http://csharp.net-informations.com/communications/csharp-multi-threaded-server-socket.htm
 * 
 * MySQL
 * 1.  http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL
 * 
 * Thread Synchronization
 * 1.   http://msdn.microsoft.com/en-us/library/c5kehkcz.aspx
 * 2.   http://www.brainbell.com/tutors/C_Sharp/Multithreaded_Programming.htm
 * 3.   http://www.c-sharpcorner.com/UploadFile/1d42da/threading-with-mutex/
 * 
 * 
 * 
 * Revision history
 * 20/03/2013   version1.0 #Publish version to wuttidsak, It's confirm from K.tae
 * 
 */

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Threading;
using System.Net.Sockets;
using MySql.Data.MySqlClient;

namespace wtsServer_service
{
    class mainProgram
    {
        static void Main(string[] args)
        {
            Console.WriteLine("wtsServer_service version 1.0");
            Server tcpServer = new Server();
        }
    }

    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        int lineCount = 0;
        string  line;
        string[] lineBuf = new string[8];
        string config_serverIP;
        string config_serverPort;
        string config_dbUser;
        string config_dbPass;
        string config_dbName;
        string config_nodeTimeout;

        string ConnectionString;
        string queryString;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            System.IO.StreamReader file = new System.IO.StreamReader("config.txt");
            while ((line = file.ReadLine()) != null)
            {
                int start   = line.IndexOf("=");
                int end     = line.IndexOf(";");
                string resultSub    = line.Substring(start + 1, end - start - 1);
                lineBuf[lineCount]  = resultSub;
                //Console.WriteLine(line);
                //Console.WriteLine(lineBuf[lineCount]);

                lineCount++;
            }

            config_serverIP     = lineBuf[0];
            config_serverPort   = lineBuf[1];
            config_dbUser       = lineBuf[2];
            config_dbPass       = lineBuf[3];
            config_dbName       = lineBuf[4];
            config_nodeTimeout  = lineBuf[5];

            Console.WriteLine("Following line is config");
            Console.WriteLine(config_serverIP);
            Console.WriteLine(config_serverPort);
            Console.WriteLine(config_dbUser);
            Console.WriteLine(config_dbPass);
            Console.WriteLine(config_dbName);
            Console.WriteLine(config_nodeTimeout);

            ConnectionString =  "SERVER="   + config_serverIP   + ";" +
                                "DATABASE=" + config_dbName     + ";" +
                                "UID="      + config_dbUser     + ";" +
                                "PASSWORD=" + config_dbPass     + ";";

            connection = new MySqlConnection(ConnectionString);
        }

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close connection
        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
            
        //Insert statement
        public void Insert(String queryString)
        {
            lock (this)
            {
                //open connection
                if (this.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(queryString, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    this.CloseConnection();
                }
            }
        }

        //Update statement
        public void Update(String queryString)
        {
            lock (this)
            {
                //Open connection
                if (this.OpenConnection() == true)
                {
                    //create mysql command
                    MySqlCommand cmd = new MySqlCommand();
                    //Assign the query using CommandText
                    cmd.CommandText = queryString;
                    //Assign the connection using Connection
                    cmd.Connection = connection;

                    //Execute query
                    cmd.ExecuteNonQuery();

                    //close connection
                    this.CloseConnection();
                }
            }
        }

        //Delete statement
        public void Delete()
        {
            lock (this)
            {
                string query = "DELETE FROM tableinfo WHERE name='John Smith'";

                if (this.OpenConnection() == true)
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.ExecuteNonQuery();
                    this.CloseConnection();
                }
            }
        }

        //Count statement
        public int Count(String queryString)
        {
            lock (this)
            {
                int Count = -1;

                //Open Connection
                if (this.OpenConnection() == true)
                {
                    //Create Mysql Command
                    MySqlCommand cmd = new MySqlCommand(queryString, connection);

                    //ExecuteScalar will return one value
                    Count = int.Parse(cmd.ExecuteScalar() + "");

                    //close Connection
                    this.CloseConnection();

                    return Count;
                }
                else
                {
                    return Count;
                }
            }
        }

        //Check table and Create it if not exist
        public int CkeckOrCreateTableIfNotExist(int tableName)
        {
            lock (this)
            {
                int countRow;
                try
                {
                    queryString = "SELECT COUNT(*) FROM wts_node_"
                                + tableName
                                + "";
                    countRow = Count(queryString);
                    Console.WriteLine("This table have been created, Number of record is {0}", countRow);
                    return countRow;
                }
                catch (MySqlException exMySQL)
                {
                    countRow = 0;
                    //Close connection in case cmd error.
                    this.CloseConnection();

                    //Case error of exception.
                    switch (exMySQL.Number)
                    {
                        case 1146:
                            Console.WriteLine("Not existing the table");
                            //CREATE TABLE wts_node_11 LIKE wts_node_xx
                            queryString = "CREATE TABLE wts_node_"
                                        + tableName
                                        + " LIKE wts_node_xx";
                            Insert(queryString);
                            Console.WriteLine("wts_node_{0} is Created", tableName);
                            break;
                        default:
                            Console.WriteLine(exMySQL.Message);
                            break;
                    }
                    return countRow;
                }
            }
        }
    }

    class Server
    {
        private TcpListener tcpListener;
        private Thread      listenThread;
        private int         errorCount;
        DBConnect dbMySQL = new DBConnect();

        public Server()
        {
            errorCount          = 0;
            this.tcpListener    = new TcpListener(IPAddress.Any, 8082);
            this.listenThread   = new Thread(new ThreadStart(ListenForClients));
            this.listenThread.Start();
            Console.WriteLine("TCP ip Server started...");
        }

        private void ListenForClients()
        {
            this.tcpListener.Start();
            int countClient = 0;

            while (true)
            {
                countClient++;
                //blocks until a client has connected to the server
                TcpClient clientSocket = this.tcpListener.AcceptTcpClient();
                Console.WriteLine("Conected by client {0}", countClient);

                //create a thread to handle communication 
                //with connected client
                Thread clientThread = new Thread(delegate()
                {
                    HandleClientComm(clientSocket, countClient);
                });
                clientThread.Start();
            }
            
        }

        private void HandleClientComm(TcpClient clientSocket, int nConnection)
        {
            TcpClient       tcpClient       = (TcpClient)clientSocket;
            NetworkStream   clientStream    = tcpClient.GetStream();

            byte[]  message = new byte[4096];
            int     bytesRead;

            Console.WriteLine("Connection number {0}", nConnection);

            while (true)
            {
                bytesRead = 0;

                try
                {
                    //blocks until a client sends a message
                    bytesRead = clientStream.Read(message, 0, 4096);
                }
                catch
                {
                    //a socket error has occured
                    Console.WriteLine("Conection {0} error.", nConnection);
                    break;
                }

                if (bytesRead == 0)
                {
                    //the client has disconnected from the server
                    Console.WriteLine("Connection {0} disconect.", nConnection);
                    break;
                }

                //message has successfully been received
                ASCIIEncoding encoder = new ASCIIEncoding();
                String dataFromClient = encoder.GetString(message, 0, bytesRead);
                Console.Write(dataFromClient);

                //Check package type of packgag an insert/update to db MySQL.
                int countComma = 0;
                for (int i = 0; i<dataFromClient.Length; i++)
                {
                    if (dataFromClient[i] == ',')
                    {
                        countComma++;
                    }
                }
                if (countComma > 1)
                {
                    insertDataToMySQL(dataFromClient);
                }
                else
                {
                    insertAliveNodeToMySQL(clientSocket, dataFromClient);
                }   

                //reply message.
                byte[] buffer = encoder.GetBytes("200\r\n");
                clientStream.Write(buffer, 0, buffer.Length);
                clientStream.Flush();
                Console.WriteLine("Reply...");
            }

            tcpClient.Close();
        }

        public static bool IsCharDigit(char c)
        {
            return ((c >= '0') && (c <= '9'));
        }

        void insertDataToMySQL(string dataFromClient)
        {
            try
            {
                int _boxID       = 1;
                int _event       = 2;
                int _number      = 3;
                int _section     = 4;
                int _group       = 5;
                int _channel     = 6;
                char _subNumber = '0';
                string   queryString;
                string[] words;               

                words   = dataFromClient.Split(',');
                _boxID  = Convert.ToInt32(words[0].TrimStart('@'));
                Console.Write("_boxID    ");
                Console.WriteLine(_boxID);
                _event  = Convert.ToInt32(words[1]);
                Console.Write("_event    ");
                Console.WriteLine(_event);

                string[] timestamp  = words[2].Split(' ');
                string[] myDay      = timestamp[0].Split('/');
                string[] myTime     = timestamp[1].Split(':');

                DateTime dt = new DateTime(Convert.ToInt32(myDay[2]) + 2000,
                                           Convert.ToInt32(myDay[1]),
                                           Convert.ToInt32(myDay[0]),
                                           Convert.ToInt32(myTime[0]),
                                           Convert.ToInt32(myTime[1]),
                                           Convert.ToInt32(myTime[2]));

                string _timeStamp = String.Format("{0:yyyy-MM-dd HH:mm:ss}", dt);

                string _theDate  = String.Format("{0:yyyy-MM-dd}", dt);
                Console.WriteLine("_Date     {0}", _theDate);

                string _theTime = String.Format("{0:HH:mm:ss}", dt);
                Console.WriteLine("_Time     {0}", _theTime);

                string tempNumber = words[3];
                char headerNumber = tempNumber[0];
                if(IsCharDigit(headerNumber))
                {
                    _number = Convert.ToInt32(words[3]);
                    Console.WriteLine("_number {0}", _number);
                    _subNumber = '0';
                }
                else
                {
                    _number = Convert.ToInt32(tempNumber.Remove(0, 1));
                    Console.WriteLine("_number   {0}", _number);

                    _subNumber = headerNumber;
                    Console.WriteLine("_subNum   {0}", _subNumber);
                }

                _section = Convert.ToInt32(words[4]);
                Console.Write("_section  ");
                Console.WriteLine(_section);

                _group = Convert.ToInt32(words[5]);
                Console.Write("_group    ");
                Console.WriteLine(_group);

                _channel = Convert.ToInt32(words[6]);
                Console.Write("_channel  ");
                Console.WriteLine(_channel);

                //Keep raw package, every event, every package.               
                queryString = "INSERT INTO wts_dataloger(boxID, event, timestamp, number, section, groupService, channel, subNumber)"
                            + "VALUES ('" 
                            + _boxID 
                            + "','" 
                            + _event 
                            + "','" 
                            + _timeStamp 
                            + "','" 
                            + _number 
                            + "','" 
                            + _section 
                            + "','" 
                            + _group 
                            + "','" 
                            + _channel 
                            + "','" 
                            + _subNumber 
                            + "')";
                dbMySQL.Insert(queryString);


                /*
                 * 
                 * Compose package before keep indatabase.         
                 * 
                 */                
                dbMySQL.CkeckOrCreateTableIfNotExist(_boxID);

                //Query logger for Insert or Update data
                queryString = "SELECT COUNT(*) FROM wts_node_"
                            + _boxID
                            + " WHERE "
                            + "sub_number='"
                            + _subNumber
                            + "' AND "
                            + "number="
                            + _number
                            + " AND "
                            + "date_service='"
                            + _theDate
                            + "' AND "
                            + "section_service="
                            + _section
                            + " AND "
                            + "group_service="
                            + _group
                            + "";
                int countRow = dbMySQL.Count(queryString);
                //Console.WriteLine(countRow);

                string typeOfTime;
                if (_event == 1)
                {
                    typeOfTime = "start_time";
                }
                else if (_event == 2)
                {
                    typeOfTime = "start_service";
                }
                else if (_event == 3)
                {
                    typeOfTime = "end_service";
                }
                else
                {
                    typeOfTime = "ERROR.";
                }

                if (countRow == 0)
                { 
                    queryString = "INSERT INTO wts_node_"
                                + _boxID
                                + "(date_service, sub_number, number, section_service, group_service, "
                                + typeOfTime
                                + ", "
                                + "channel"
                                + ")"
                                + " VALUES ('"
                                + _theDate
                                + "','"
                                + _subNumber
                                + "','"
                                + _number
                                + "','"
                                + _section
                                + "','"
                                + _group
                                + "','"
                                + _theTime
                                + "','"
                                + _channel
                                + "')";
                    dbMySQL.Insert(queryString);

                }
                else 
                {
                    queryString = "UPDATE wts_node_"
                                + _boxID
                                + " SET "
                                + typeOfTime
                                + "='"
                                + _theTime
                                + "'"
                                + " WHERE "
                                + "date_service='"
                                + _theDate
                                + "' AND "
                                + "sub_number='"
                                + _subNumber
                                + "' AND "
                                + "number="
                                + _number
                                + " AND "
                                + "section_service="
                                + _section
                                + " AND "
                                + "group_service="
                                + _group
                                + "";

                    dbMySQL.Insert(queryString);                    
                }

            }
            catch(Exception ex)
            {               
                Console.WriteLine("----------------Package ERROR!!!------------------");
                Console.WriteLine(ex);
                errorCount++;
                Console.WriteLine(errorCount);

                dbMySQL.CloseConnection();

                //Keep error timestamp and package to log file.
                DateTime    time = DateTime.Now;
                string      systemTimeStamp = String.Format("{0:yyyy/MM/dd HH:mm:ss}", time);
                string      path = @"errorLog.txt";
                /*
                using (StreamWriter w = File.AppendText(path))
                {
                    w.WriteLine();
                    Console.WriteLine(systemTimeStamp);
                    w.Write(DateTime.Now.ToString("yyyy-MMM-dd_HH.mm.ss, "));
                    w.Write(errorCount);
                    w.Write(", ");
                    w.Write(dataFromClient);
                    w.WriteLine(ex);
                    w.Close();
                }
                 */

                return;
            }
        }

        void insertAliveNodeToMySQL(TcpClient clientSocket, string dataFromClient)
        {
            // Perform database operations 
            string      queryString;
            DateTime    time        = DateTime.Now;
            string      timeStamp   = String.Format("{0:yyyy/MM/dd HH:mm:ss}", time);
            //Console.WriteLine(time_started);
            
            string[]    words   = dataFromClient.Split(',');
            int         _boxID  = Convert.ToInt32(words[0].TrimStart('@'));
            string      _nodeIP = ((IPEndPoint)clientSocket.Client.RemoteEndPoint).Address.ToString();

            //Query for count each alive node in record.
            queryString     = "SELECT COUNT(id) FROM  wts_status_node WHERE boxID=" 
                            + _boxID 
                            + "";
            int countIsInDB = dbMySQL.Count(queryString);
            //Check number of alive node each record.
            if (countIsInDB == 0)
            {
                //SQL Command Insert new node.
                queryString = "INSERT INTO wts_status_node (boxID, nodeIP, timeActive)"
                            + "VALUES ('" 
                            + _boxID 
                            + "','" 
                            + _nodeIP 
                            + "','" 
                            + timeStamp 
                            + "')";

                dbMySQL.Insert(queryString);
                Console.WriteLine("Active, Inserted new boxID {0}", _boxID);
            }
            else
            {
                //SQL Command Update.
                queryString = "UPDATE wts_status_node SET nodeIP='" 
                            + _nodeIP 
                            + "', timeActive='" 
                            + timeStamp 
                            + "' WHERE boxID='" 
                            + _boxID 
                            + "'";

                dbMySQL.Update(queryString);
                Console.WriteLine("Active, Updated boxID {0}", _boxID);
            }
        } 
    }
}